/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package merlion.mrp.ppm.session;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import javax.ejb.EJB;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import merlion.common.util.Consts;
import merlion.mrp.ppm.entity.ArchivedIndivSOP;
import merlion.mrp.ppm.entity.BOM;
import merlion.mrp.ppm.entity.DailyMPS;
import merlion.mrp.ppm.entity.DailyPlannedProduction;
import merlion.mrp.ppm.entity.IndivSOP;
import merlion.mrp.ppm.entity.MRP;
import merlion.mrp.ppm.entity.Product;
import merlion.mrp.ppm.entity.Reservation;
import merlion.mrp.ppm.entity.SOP;
import merlion.mrp.ppm.entity.WeeklyMPS;
import merlion.mrp.ppm.pojo.ScheduleResultTemp;
import merlion.scm.lcm.entity.DeliveryOrder;

/**
 *
 * @author Zhu Xin nd Zhang Ying
 */
@Stateless
public class MPSPlanningSession implements MPSPlanningSessionLocal {

    @PersistenceContext
    private EntityManager entityManager;
    @EJB
    private SalesOptPlanningSessionLocal sopSession;
    @EJB
    private MRPSessionLocal mrpSession;

    @Override
    public boolean checkWeeklyMPSAvailable(int selectedMonth, Integer selectedYear) {

        boolean available = true;

        //check whether corresponding SOP is exist
        SOP sop = getSOPForMPS(selectedMonth, selectedYear);
        if (sop == null) {
            available = false;
        }
        return available;
    }

    @Override
    public boolean checkAbleToCreateDailyMPS(int selectedYear, int selectedMonth, int selectedWeek) {

        // 1. The week selected exist on Calendar. 2.SOP/WeeklyMPS is available
        boolean valid = true;
        if (!checkWeeklyMPSAvailable(selectedMonth, selectedYear)) {
            valid = false;
        }
        int numberOfWeeks = getNumberOfWeeks(selectedYear, selectedMonth);
        if (numberOfWeeks < selectedWeek) {
            valid = false;
        }

        return valid;

    }

    //updated for Calendar
    @Override
    public int getTotalWorkingDaysForMonth(int selectedMonth, Integer selectedYear) {
        Integer numberOfWDs = 0;
        Calendar startCal;

        startCal = Calendar.getInstance();
        startCal.set(selectedYear, selectedMonth - 1, 1, 0, 0, 0); //start day of the month
        int maxDay = startCal.getActualMaximum(Calendar.DAY_OF_MONTH);  //number of days in the month
        for (int i = 0; i < maxDay; i++) {
            if (!isPublicHoliday(startCal) && startCal.get(Calendar.DAY_OF_WEEK) != Calendar.SATURDAY && startCal.get(Calendar.DAY_OF_WEEK) != Calendar.SUNDAY) {
                numberOfWDs++;
            }
            startCal.add(Calendar.DAY_OF_MONTH, 1);
        }
        return numberOfWDs;
    }

    //This method determines whether the given day is a public holiday
    private boolean isPublicHoliday(Calendar cal) {
        boolean isPH = false;
        Timestamp tsp = new Timestamp(cal.getTimeInMillis());
        Query query = entityManager.createQuery("SELECT ph FROM PublicHoliday ph WHERE ph.dateOfHoliday =:p1");
        query.setParameter("p1", tsp);
        if (!query.getResultList().isEmpty()) {
            isPH = true;
        }
        return isPH;
    }

    //get number of working days for each week in a month.
    //return arraylist of size 5
    @Override
    public ArrayList<Integer> getWorkingDaysList(int selectedMonth, Integer selectedYear) {
        int number = getNumberOfWeeks(selectedYear, selectedMonth);
        System.out.println("number of weeeks:" + number);
        ArrayList<Integer> WDs = new ArrayList<Integer>();
        Calendar startCal = getFirstDayOfMonth(selectedYear, selectedMonth);
        int day = startCal.get(Calendar.DAY_OF_WEEK); // the day of the first day
        if (day != Calendar.MONDAY) {
            int days = (Calendar.SATURDAY - day + 2) % 7;
            startCal.add(Calendar.DAY_OF_MONTH, days);  //get the first Monday of the month
        }
        for (int i = 0; i < number; i++) {
            //TODO: edited by ZY, need to be checked
            Calendar tempCal = Calendar.getInstance();
            tempCal.set(startCal.get(Calendar.YEAR), startCal.get(Calendar.MONTH), startCal.get(Calendar.DAY_OF_MONTH),
                    0, 0, 0);
            WDs.add(getTotalWorkingDaysForWeek(tempCal, 5));
            //WDs.add(getTotalWorkingDaysForWeek(startCal, 5));
            startCal.add(Calendar.DAY_OF_MONTH, 7);
        }
        if (WDs.size() < 5) {
            WDs.add(0);
        }
        return WDs;
    }

    private int getTotalWorkingDaysForWeek(Calendar cal, int days) {
        int numberOfWDs = days; //normal week, days should be 5
        for (int i = 0; i < days; i++) {
            if (isPublicHoliday(cal)) {
                numberOfWDs--;
            }
            cal.add(Calendar.DAY_OF_MONTH, 1);
        }
        return numberOfWDs;
    }

    //return dates of a week
    @Override
    public ArrayList<Timestamp> getDatesforWeek(int selectedYear, Integer selectedMonth, Integer selectedWeek) {
        Calendar startCal = getFirstMondayOfMonth(selectedYear, selectedMonth); //first Monday of a month
        startCal.add(Calendar.WEEK_OF_YEAR, selectedWeek - 1); //get the start day of the week
        ArrayList<Timestamp> dates = new ArrayList<Timestamp>();
        for (int i = 0; i < 5; i++) {
            dates.add(new Timestamp(startCal.getTime().getTime()));
            startCal.add(Calendar.DAY_OF_MONTH, 1); //next day
        }
        return dates;
    }

    @Override
    public ArrayList<ArrayList<Long>> getEachDayDemandForOneWeek(int selectedYear, Integer selectedMonth, Integer selectedWeek) {
        //return matrix of 4*5

        int usedMonth = 0, usedYear = 0;
        if (selectedMonth + 1 > 12) {
            usedMonth = (selectedMonth + 1) % 12;
            usedYear = selectedYear + 1;
        } else {
            usedMonth = selectedMonth + 1;
            usedYear = selectedYear;
        }

        ArrayList<ArrayList<Long>> dailyDemand = new ArrayList<ArrayList<Long>>();
        ArrayList<Long> allDailyDemand = getDailyDemandForAll(selectedYear, selectedMonth);  //average daily demand for all product in current month
        ArrayList<Long> allDailyDemandNext = getDailyDemandForAll(usedYear, usedMonth);  //average daily demand for all product next month
        Calendar startCal = getFirstMondayOfMonth(selectedYear, selectedMonth);
        Calendar endCal = getLastDayOfMonth(selectedYear, selectedMonth);
        startCal.add(Calendar.WEEK_OF_YEAR, selectedWeek - 1); //get the start day of the week
        for (int i = 0; i < 4; i++) {
            ArrayList<Long> dailyDemandForEach = new ArrayList<Long>();  //daily demand for each product
            for (int j = 0; j < 5; j++) {
                if (isPublicHoliday(startCal)) {
                    dailyDemandForEach.add(new Long(0));  //no demand on that day
                } else if (startCal.get(Calendar.MONTH) == endCal.get(Calendar.MONTH)) {  //the date is in the current month
                    dailyDemandForEach.add(allDailyDemand.get(i));
                } else {
                    dailyDemandForEach.add(allDailyDemandNext.get(i));
                }
                startCal.add(Calendar.DAY_OF_MONTH, 1); //next day
            }
            dailyDemand.add(dailyDemandForEach);
            //  edited by ZY (should be correct)
            startCal = getFirstMondayOfMonth(selectedYear, selectedMonth);
            startCal.add(Calendar.WEEK_OF_YEAR, selectedWeek - 1); //get the start day of the week
        }
        return dailyDemand;
    }

    private ArrayList<Long> getDailyDemandForAll(int selectedYear, int selectedMonth) {
        ArrayList<Long> monthlyDemand = getMonthlyDemand(selectedMonth, selectedYear);
        int numberOfDays = getTotalWorkingDaysForMonth(selectedMonth, selectedYear);
        ArrayList<Long> dailyDemand = new ArrayList<Long>();
        for (int i = 0; i < monthlyDemand.size(); i++) {
            Double demand = (Double) monthlyDemand.get(i).doubleValue() / numberOfDays;
            dailyDemand.add(Math.round(demand));
        }
        return dailyDemand;
    }

    public Calendar getFirstMondayOfMonth(int selectedYear, int selectedMonth) {
        Calendar startCal = getFirstDayOfMonth(selectedYear, selectedMonth); // first day of a month
        int day = startCal.get(Calendar.DAY_OF_WEEK); // the day of the first day
        if (day != Calendar.MONDAY) {
            int days = (Calendar.SATURDAY - day + 2) % 7;
            startCal.add(Calendar.DAY_OF_MONTH, days);  //get the first Monday of the month
        }
        return startCal;
    }

    @Override
    public ArrayList<ArrayList<Long>> getEachWeekDemandForOneMonth(int selectedMonth, int selectedYear) {
        ArrayList<ArrayList<Long>> weeklyDemand = new ArrayList<ArrayList<Long>>();

        WeeklyMPS wmps = getWeeklyMPS(selectedYear, selectedMonth);
        for (int i = 0; i < 4; i++) {
            ArrayList<Long> indivWeeklyDemand = new ArrayList<Long>();
            for (int j = 0; j < 5; j++) {
                indivWeeklyDemand.add(wmps.returnProductIndivSOP(i).getWeeklyDemandList().get(j).getLongValue());
            }
            weeklyDemand.add(indivWeeklyDemand);
        }
        return weeklyDemand;
    }

    @Override
    public ArrayList<ArrayList<Long>> getWeeklyDemandForOneMonth(int selectedMonth, Integer selectedYear) {
        // return arraylist of size 4*5 , the outer array is for product, 0-A, 1-B, 2-C, 3-D; the inner size depends on the # of weeks in selected month

        ArrayList<ArrayList<Long>> weeklyDemand = new ArrayList<ArrayList<Long>>();
        ArrayList<Long> indivWeeklyDemand = new ArrayList<Long>();  //individual weekly demand for every product
        ArrayList<Long> monthlyDemand = getMonthlyDemand(selectedMonth, selectedYear); //monthly demand for all products (current month)
        int usedMonth = 0, usedYear = 0;
        if (selectedMonth + 1 > 12) {
            usedMonth = (selectedMonth + 1) % 12;
            usedYear = selectedYear + 1;
        } else {
            usedMonth = selectedMonth + 1;
            usedYear = selectedYear;
        }
        ArrayList<Long> monthlyDemandNext = getMonthlyDemand(usedMonth, usedYear); //monthly demand for all products (next month)

        int numberOfWeeks = getNumberOfWeeks(selectedYear, selectedMonth);  //number of weeks in a month
        int numberOfDays = getTotalWorkingDaysForMonth(selectedMonth, selectedYear); //number of weekdays in a month

        int numberOfDaysNext = getTotalWorkingDaysForMonth(usedMonth, usedYear); //number of weekdays in the next month


        Calendar startCal = getFirstDayOfMonth(selectedYear, selectedMonth); // first day of a month
        Calendar endCal = getLastDayOfMonth(selectedYear, selectedMonth); //last day of a month
        int day = startCal.get(Calendar.DAY_OF_WEEK); // the day of the first day
        if (day != Calendar.MONDAY) {
            int days = (Calendar.SATURDAY - day + 2) % 7;
            startCal.add(Calendar.DAY_OF_MONTH, days);  //get the first Monday of the month
        }


        int numberOfWDs = 0;
        int numberOfWDsNext = 0;
        Long indivMonthlyDemand;
        Double indivDailyDemand;
        Double indivDailyDemandNext;
        Double amountLast = new Double(0); //amount of last week

        int dayOfLast = endCal.get(Calendar.DAY_OF_WEEK); //day of the last day of the month

        Calendar lastMonday = endCal;
        lastMonday.add(Calendar.DAY_OF_YEAR, (dayOfLast * -1) + 1); //the monday of the last week of the month

        if (dayOfLast == Calendar.FRIDAY || dayOfLast == Calendar.SATURDAY || dayOfLast == Calendar.SUNDAY) {
            numberOfWDs = getTotalWorkingDaysForWeek(lastMonday, 5);
        } else {  //last day is Monday, Tuesday, Wednesday or Thursday
            int count = dayOfLast - Calendar.MONDAY + 1; //calculate number of working days remaining in the current month
            numberOfWDs = getTotalWorkingDaysForWeek(lastMonday, count);
            lastMonday.set(selectedYear, selectedMonth + 1 - 1, 1, 0, 0, 0); //the first day of last month  updated for calendar
            numberOfWDsNext = getTotalWorkingDaysForWeek(lastMonday, 5 - count);
        }

        for (int i = 0; i < 4; i++) {
            indivMonthlyDemand = monthlyDemand.get(i);

            indivDailyDemand = (double) indivMonthlyDemand / numberOfDays;
            indivDailyDemandNext = (double) monthlyDemandNext.get(i) / numberOfDaysNext;
            for (int j = 0; j < numberOfWeeks - 1; j++) {
                int weekdays = getTotalWorkingDaysForWeek(startCal, 5);
                indivWeeklyDemand.add(Math.round(weekdays * indivDailyDemand)); //demand for a particular product in one week
                System.out.println("testing weekly demand: " + (weekdays * indivDailyDemand));

                startCal.add(Calendar.DAY_OF_MONTH, 7); //go to next week
            }
            //demand of the last week may includes demand from the next month

            amountLast = numberOfWDs * indivDailyDemand + numberOfWDsNext * indivDailyDemandNext;
            indivWeeklyDemand.add(Math.round(amountLast));


            weeklyDemand.add(indivWeeklyDemand);
            indivWeeklyDemand = new ArrayList<Long>();
        }
        if (weeklyDemand.get(0).size() < 5) {
            for (int i = 0; i < 4; i++) {
                weeklyDemand.get(i).add(null);
            }
        }

        for (int i = 0; i < 4; i++) {
            for (int j = 0; j < 5; j++) {
                System.out.println("testing for demand haha: " + weeklyDemand.get(i).get(j));
            }
        }

        return weeklyDemand;
    }

    //Calendar updated
    public Calendar getFirstDayOfMonth(int year, int month) {

        Calendar startCal = Calendar.getInstance();
        startCal.set(year, month - 1, 1, 0, 0, 0); // first day of a month
        return startCal;
    }

    //Calendar updated
    private Calendar getLastDayOfMonth(int year, int month) {
        Calendar cal = getFirstDayOfMonth(year, month);
        int maxDay = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
        cal.set(year, month - 1, maxDay, 0, 0, 0);
        return cal;
    }

    @Override
    public Integer getNumberOfWeeks(int year, int month) {
        Integer count = 0;
        Calendar startCal;
        Calendar endCal;

        startCal = getFirstMondayOfMonth(year, month); //first Monday of the month
        endCal = getLastDayOfMonth(year, month); //last day of the month

        int startWeek = startCal.get(Calendar.WEEK_OF_YEAR); //week No. of the first day of the month
        int endWeek = endCal.get(Calendar.WEEK_OF_YEAR); //week No. of the last day of the month
        System.out.println("start week : " + startWeek + " end week: " + endWeek);
        if (startWeek <= endWeek) {
            count = endWeek - startWeek + 1;
        } else {
            count = startCal.getActualMaximum(Calendar.WEEK_OF_YEAR) + endWeek - startWeek + 1;
        }

        return count;
    }

    @Override
    public SOP getSOPForMPS(int selectedMonth, Integer selectedYear) {
        int startMonth;
        if (selectedMonth <= 3) {
            startMonth = 1;
        } else if (selectedMonth <= 6) {
            startMonth = 4;
        } else if (selectedMonth <= 9) {
            startMonth = 7;
        } else {
            startMonth = 10;
        }

        SOP sop = sopSession.getSOP(selectedYear, startMonth);
        return sop;
    }

    @Override
    public ArrayList<Long> getMonthlyDemand(int selectedMonth, Integer selectedYear) {

        // return arraylist of size 4, 0-A,2 1-B, 2-C, 3-D

        int startMonth;
        if (selectedMonth <= 3) {
            startMonth = 1;
        } else if (selectedMonth <= 6) {
            startMonth = 4;
        } else if (selectedMonth <= 9) {
            startMonth = 7;
        } else {
            startMonth = 10;
        }

        int order = selectedMonth - startMonth;

        ArrayList<Long> monthlyDemands = new ArrayList<Long>();  //the arraylist to return
        SOP sop = sopSession.getSOP(selectedYear, startMonth);
        for (int i = 0; i < 4; i++) {
            if (sop == null) {
                monthlyDemands.add(new Long(0));
            } else {
                monthlyDemands.add(sop.returnProductIndivSOP(i).getProductionPlanList().get(order).getLongValue());
            }
        }
        return monthlyDemands;

    }

    @Override
    public ArrayList<ArrayList<Timestamp>> getStartEndDaysList(int month, Integer selectedYear) {
        // return array of size 2 * 5 ; the inner array: all start days on one row, all end days on one day
        System.out.println("month : " + month);
        int number = getNumberOfWeeks(selectedYear, month);
        ArrayList<Timestamp> startDates = new ArrayList<Timestamp>();
        ArrayList<Timestamp> endDates = new ArrayList<Timestamp>();

        Calendar startCal = getFirstDayOfMonth(selectedYear, month);
        int day = startCal.get(Calendar.DAY_OF_WEEK); // the day of the first day
        if (day != Calendar.MONDAY) {
            int days = (Calendar.SATURDAY - day + 2) % 7;
            startCal.add(Calendar.DAY_OF_MONTH, days);  //get the first Monday of the month
        }
        System.out.println("start cal 1: " + Consts.sdfMonth.format(startCal.getTime().getTime()));
        for (int i = 0; i < number; i++) {
            startDates.add(new Timestamp(startCal.getTime().getTime()));
            System.out.println("start dates: " + Consts.sdfMonth.format(startDates.get(i)));
            System.out.println("start cal: " + Consts.sdfMonth.format(startCal.getTime().getTime()));
            startCal.add(Calendar.DAY_OF_MONTH, 4);
            endDates.add(new Timestamp(startCal.getTime().getTime()));
            System.out.println("END dates: " + Consts.sdfMonth.format(endDates.get(i)));
            System.out.println("start cal: " + startCal.get(Calendar.MONTH));
            startCal.add(Calendar.DAY_OF_MONTH, 3);

        }
        ArrayList<ArrayList<Timestamp>> seDays = new ArrayList<ArrayList<Timestamp>>();
        seDays.add(startDates);
        seDays.add(endDates);

        if (seDays.get(0).size() < 5) {
            seDays.get(0).add(null);
            seDays.get(1).add(null);
        }

        for (int i = 0; i < 4; i++) {
            System.out.println("lajal: "
                    + Consts.sdfMonth.format(seDays.get(0).get(i)));
        }

        return seDays;
    }

    @Override
    public Product getProduct(int i) {
        String name = "";
        switch (i) {
            case 0:
                name = Consts.ProductType.BAR_A.getText();
                break;
            case 1:
                name = Consts.ProductType.BAR_B.getText();
                break;
            case 2:
                name = Consts.ProductType.BAR_C.getText();
                break;
            case 3:
                name = Consts.ProductType.BAR_D.getText();
                break;
            default:
                return null;
        }
        Query query = entityManager.createQuery("SELECT p FROM Product p WHERE p.productType=:p1");
        query.setParameter("p1", name);
        return (Product) query.getSingleResult();
    }

    public List<MRP> updateMRPTriggeredByWMPS(WeeklyMPS wmps) {
        List<MRP> mrps = getMRP(wmps.getStartPeriodYear(), wmps.getStartPeriodMonth());
        if (mrps == null) {
            return null;
        }
        //  update mrp - update gross requirements & inventory
        for (MRP m : mrps) {
            ArrayList<ArrayList<Long>> weeklyDemands = getEachWeekDemandForOneMonth(m.getStartPeriodMonth(), m.getStartPeriodYear());
            int numOfWks = m.getGrossRequirements().size();
            for (int dayId = 0; dayId < numOfWks; dayId++) {
                // for gross req
                double sum = 0;

                for (int i = 0; i < 4; i++) {
                    double kg;
                    if (i == 0 || i == 1) {
                        kg = 12;
                    } else {
                        kg = 13.32;
                    }
                    if (weeklyDemands.get(i).get(dayId) == null) {
                        sum += 0;
                    } else {
                        sum += weeklyDemands.get(i).get(dayId) * kg * m.getBom().getMaterialRequirements().get(i).getValue();
                    }
                }
                Long result = Math.round(Math.ceil(sum));
                m.getGrossRequirements().get(dayId).setLongValue(result);
                // for onhand inventory
                Long prev = mrpSession.getPlannedInventoryForPrevWeek(m.getStartPeriodYear(), m.getStartPeriodMonth(), m.getBom());
                for (int i = 0; i < numOfWks; i++) {
                    // set planned inventory
                    Long prevInv;
                    if (i == 0) {
                        prevInv = prev;
                    } else {
                        prevInv = m.getPlannedInventory().get(i - 1).getLongValue();
                    }
                    Long diff = prevInv + m.getPlannedReceipts().get(i).getLongValue() - m.getGrossRequirements().get(i).getLongValue();
                    m.getPlannedInventory().get(i).setLongValue(diff);
                }
            }
        }
        return mrps;
    }

    public List<MRP> getMRP(Integer selectedYear, Integer selectedMonth) {
        try {
            Query query = entityManager.createQuery("SELECT mrp FROM MRP mrp WHERE mrp.startPeriodYear=:p1 AND mrp.startPeriodMonth=:p2");
            query.setParameter("p1", selectedYear);
            query.setParameter("p2", selectedMonth);
            if (query.getResultList().isEmpty()) {
                return null;
            }
            return (List<MRP>) query.getResultList();
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }

    //update WeeklyMPS after DailyMPS has been changed
    @Override
    public WeeklyMPS updateWMPSTriggerByDMPS(DailyMPS newDMPS) {
        System.out.println("for debugging: enter the method 532 mps plan session***********************");
        WeeklyMPS wmps = getWeeklyMPS(newDMPS.getStartPeriodYear(), newDMPS.getStartPeriodMonth());
        int weekNO = newDMPS.getStartPeriodDate() - 1;
        for (int i = 0; i < 4; i++) {
            System.out.println(" for debugging : enter for at line 536");
            Long amount = new Long(0);
            for (int j = 0; j < 5; j++) {
                amount += newDMPS.returnIndivDMPS(i).returnDailyPlannedProduction(j).getTotalDemand();
                System.out.println("Amount at mps Session line 540" + amount);
            }
            System.out.println("Amount at mps Session line 542: preparing to set");
            wmps.returnProductIndivSOP(i).getWeeklyDemandList().get(weekNO).setLongValue(amount);

        }
        Query query = entityManager.createQuery("SELECT dmps FROM WeeklyMPS dmps WHERE dmps.startPeriodYear>:p1 OR(dmps.startPeriodYear=:p1 AND dmps.startPeriodMonth>=:p2)");
        query.setParameter("p1", newDMPS.getStartPeriodYear());
        query.setParameter("p2", newDMPS.getStartPeriodMonth());
        ArrayList<WeeklyMPS> wmpss = new ArrayList<WeeklyMPS>(query.getResultList());
        System.out.println("for debugging: how about wmpss");
        if (wmpss.isEmpty()) {
            System.out.println("for debugging: wmpss array is empty for year:" + newDMPS.getStartPeriodYear() + " for mth" + newDMPS.getStartPeriodMonth());
        } else {
            System.out.println("for debugging: ");
        }
        for (WeeklyMPS mps : wmpss) {
            updateMRPTriggeredByWMPS(mps);
        }
        List<MRP> mrps = getMRP(wmps.getStartPeriodYear(), wmps.getStartPeriodMonth());
        if (mrps != null) {
            wmps.setMrps(mrps);
        }
        entityManager.merge(wmps);
        return wmps;

    }

    private SOP archiveSOP(SOP sop) {
        //archive sop
        int archivedTimes;
        if (sop.returnProductIndivSOP(0).getArchivedIndivSops() == null || sop.returnProductIndivSOP(0).getArchivedIndivSops().isEmpty()) {
            archivedTimes = 0;
        } else {
            archivedTimes = sop.returnProductIndivSOP(0).getArchivedIndivSops().size();
        }
        for (int i = 0; i < 4; i++) {
            IndivSOP indivSOP = sop.returnProductIndivSOP(i);
            ArchivedIndivSOP archivedIndiv = new ArchivedIndivSOP(archivedTimes, indivSOP.getSalesforcastValues(), indivSOP.getUnplannedDemandList(), indivSOP.getProductionPlanList(), indivSOP.getInventoryList());
            indivSOP.getArchivedIndivSops().add(archivedIndiv);
        }
        sop = entityManager.merge(sop);
        entityManager.flush();
        return sop;
    }

    public SOP updateSOPTriggerByDMPS(DailyMPS newDMPS, ArrayList<ArrayList<Long>> prevDailyDemandList, ArrayList<ArrayList<Long>> prevDailyOTDemandList) {
        System.out.println("574 *************** mps plann enter update sop -< dmps");
        Calendar startDate = getFirstMondayOfMonth(newDMPS.getStartPeriodYear(), newDMPS.getStartPeriodMonth());
        startDate.add(Calendar.WEEK_OF_YEAR, newDMPS.getStartPeriodDate() - 1);  //start date of the dailyMPS
        Calendar endDate = getFirstMondayOfMonth(newDMPS.getStartPeriodYear(), newDMPS.getStartPeriodMonth());
        endDate.add(Calendar.WEEK_OF_YEAR, newDMPS.getStartPeriodDate() - 1);
        endDate.add(Calendar.DAY_OF_MONTH, 4);  //end date of the dailyMPS;

        SOP sop = getSOPForMPS(newDMPS.getStartPeriodMonth(), newDMPS.getStartPeriodYear());  //old sop

        sop = archiveSOP(sop);

        int month = startDate.get(Calendar.MONTH);
        int monthNo = newDMPS.getStartPeriodMonth() - sop.getStartPeriodMonth();
        Long newDiff = 0l;
        for (int i = 0; i < 4; i++) { //for each product

            for (int j = 0; j < 5; j++) { //for each day
                if (startDate.get(Calendar.MONTH) == month) {
                    newDiff += newDMPS.returnIndivDMPS(i).returnDailyPlannedProduction(j).getTotalDemand() - (prevDailyDemandList.get(i).get(j) + prevDailyOTDemandList.get(i).get(j));
                }
                startDate.add(Calendar.DAY_OF_MONTH, 1);
            }
            Long newUnplanned = newDiff + sop.returnProductIndivSOP(i).getUnplannedDemandList().get(monthNo).getLongValue();
            Long newPlanned = sop.returnProductIndivSOP(i).getProductionPlanList().get(monthNo).getLongValue() + newDiff;
            sop.returnProductIndivSOP(i).getUnplannedDemandList().get(monthNo).setLongValue(newUnplanned);  //update unplanned demand
            sop.returnProductIndivSOP(i).getProductionPlanList().get(monthNo).setLongValue(newPlanned); //update planned production      
            startDate = getFirstMondayOfMonth(newDMPS.getStartPeriodYear(), newDMPS.getStartPeriodMonth());
            startDate.add(Calendar.WEEK_OF_YEAR, newDMPS.getStartPeriodDate() - 1);  //start date of the dailyMPS
            newDiff = 0l;

            if (endDate.get(Calendar.MONTH) != month && (endDate.get(Calendar.MONTH) + 1 > sop.getStartPeriodMonth()) && endDate.get(Calendar.MONTH) + 1 <= sop.getStartPeriodMonth() + 2) { //daily MPS span over two months but within one SOP
                for (int j = 0; j < 5; j++) {
                    if (endDate.get(Calendar.MONTH) != month) {
                        newDiff += newDMPS.returnIndivDMPS(i).returnDailyPlannedProduction(4 - j).getTotalDemand() - (prevDailyDemandList.get(i).get(4 - j) + prevDailyOTDemandList.get(i).get(4 - j));
                    }
                    endDate.add(Calendar.DAY_OF_MONTH, -1);
                }

                newUnplanned = newDiff + sop.returnProductIndivSOP(i).getUnplannedDemandList().get(monthNo + 1).getLongValue();
                newPlanned = sop.returnProductIndivSOP(i).getProductionPlanList().get(monthNo + 1).getLongValue() + newDiff;
                sop.returnProductIndivSOP(i).getUnplannedDemandList().get(monthNo + 1).setLongValue(newUnplanned);  //update unplanned demand
                sop.returnProductIndivSOP(i).getProductionPlanList().get(monthNo + 1).setLongValue(newPlanned); //update planned production      
                startDate = getFirstMondayOfMonth(newDMPS.getStartPeriodYear(), newDMPS.getStartPeriodMonth());
                startDate.add(Calendar.WEEK_OF_YEAR, newDMPS.getStartPeriodDate() - 1);  //start date of the dailyMPS
                endDate = getFirstMondayOfMonth(newDMPS.getStartPeriodYear(), newDMPS.getStartPeriodMonth());
                endDate.add(Calendar.WEEK_OF_YEAR, newDMPS.getStartPeriodDate() - 1);
                endDate.add(Calendar.DAY_OF_MONTH, 4);
                newDiff = 0l;
            }
        }
        sop.setAbleToEdit(Boolean.FALSE);
        entityManager.merge(sop);
//        entityManager.flush();

        //check whether the MPS affect next sop
        if (startDate.get(Calendar.MONTH) != endDate.get(Calendar.MONTH) && endDate.get(Calendar.MONTH) + 1 > sop.getStartPeriodMonth() + 2) { //dailyMPS span over two SOPs
            SOP sopNext = getSOPForMPS(endDate.get(Calendar.MONTH) + 1, endDate.get(Calendar.YEAR));
            if (sopNext != null) {
                sopNext = archiveSOP(sopNext);
                for (int i = 0; i < 4; i++) { //for each product 

                    for (int j = 0; j < 5; j++) { //for each day
                        if (endDate.get(Calendar.MONTH) != month) {
                            newDiff += newDMPS.returnIndivDMPS(i).returnDailyPlannedProduction(4 - j).getTotalDemand();
                        }
                        endDate.add(Calendar.DAY_OF_MONTH, -1);
                    }

                    Long newUnplanned = newDiff + sopNext.returnProductIndivSOP(i).getUnplannedDemandList().get(0).getLongValue();
                    Long newPlanned = newDiff + sopNext.returnProductIndivSOP(i).getProductionPlanList().get(0).getLongValue();
                    sopNext.returnProductIndivSOP(i).getUnplannedDemandList().get(0).setLongValue(newUnplanned);  //update unplanned demand
                    sopNext.returnProductIndivSOP(i).getProductionPlanList().get(0).setLongValue(newPlanned);
                    startDate = getFirstMondayOfMonth(newDMPS.getStartPeriodYear(), newDMPS.getStartPeriodMonth());
                    startDate.add(Calendar.WEEK_OF_YEAR, newDMPS.getStartPeriodDate() - 1);  //start date of the dailyMPS
                    endDate = getFirstMondayOfMonth(newDMPS.getStartPeriodYear(), newDMPS.getStartPeriodMonth());
                    endDate.add(Calendar.WEEK_OF_YEAR, newDMPS.getStartPeriodDate() - 1);
                    endDate.add(Calendar.DAY_OF_MONTH, 4);
                    newDiff = 0l;
                }
                sopNext.setAbleToEdit(Boolean.FALSE);
                entityManager.merge(sopNext);
//                entityManager.flush();
            }
        }
        //WeeklyMPS wmps = getWeeklyMPS(newDMPS.getStartPeriodYear(), newDMPS.getStartPeriodMonth());  //updated WeeklMPS
        //create Archive


//        //get old amount and new amount for sop (note: for one month in SOP only)
//        int monthNo = newDMPS.getStartPeriodMonth() - sop.getStartPeriodMonth();
//        int numberOfWeeks = getNumberOfWeeks(newDMPS.getStartPeriodYear(), newDMPS.getStartPeriodMonth());
//        for (int i = 0; i < 4; i++) {
//            Long newPlanned = new Long(0);
//            Long oldPlanned = sop.returnProductIndivSOP(i).getProductionPlanList().get(monthNo).getLongValue();
//            for (int j = 0; j < numberOfWeeks; j++) {
//                newPlanned += wmps.returnProductIndivSOP(i).getWeeklyDemandList().get(j).getLongValue();  //accumulate from weeklyMPS      
//            }
//            int monthNo = newDMPS.getStartPeriodMonth() - sop.getStartPeriodMonth();
//            sop.returnProductIndivSOP(i).getUnplannedDemandList().get(monthNo).setLongValue(newUnplanned);  //update unplanned demand
//            sop.returnProductIndivSOP(i).getProductionPlanList().get(monthNo).setLongValue(newPlanned); //update planned production      
//        }





        return sop;
    }

//    @Override
//    public boolean persistDailyMPS(DailyMPS newDMPS, Long staffId) {
//        // set staff
//        try {            
//
//            
//            // UPDATED: 1. update w mps, remember to merge
//            updateWMPSTriggerByDMPS(newDMPS);
//            
//
//            // UPDATED: 2. update sop ( i)subtract old with new and get the difference as unplanned demand; ii) create archived sops, use the 3rd constructor
//            // ), remember to merge
//            
//            updateSOPTriggerByDMPS(newDMPS);
//            SOP sop = getSOPForMPS(newDMPS.getStartPeriodMonth(), newDMPS.getStartPeriodYear());
//            newDMPS.setSop(sop);
//            entityManager.merge(newDMPS);
//            
//            
//            return true;
//        } catch (Exception ex) {
//            ex.printStackTrace();
//            return false;
//        }
//    }
    @Override
    public DailyMPS getDailyMPS(Integer selectedYear, Integer selectedMonth, Integer selectedWeek) {
        try {
            Query query = entityManager.createQuery("SELECT dmps FROM DailyMPS dmps WHERE dmps.startPeriodYear=:p1 AND dmps.startPeriodMonth=:p2 AND dmps.startPeriodDate=:p3");
            query.setParameter("p1", selectedYear);
            query.setParameter("p2", selectedMonth);
            query.setParameter("p3", selectedWeek);
            if (query.getResultList().isEmpty()) {
                return null;
            }
            return (DailyMPS) query.getResultList().get(0);
        } catch (Exception ex) {
            return null;
        }
    }

    @Override
    public ScheduleResultTemp getScheduleResult(DailyMPS dailyDMPS) {
        // output is scheduleResultTemp, a pojo class
        //  fill in all the fields inside the object and return the object 
        ScheduleResultTemp result = new ScheduleResultTemp();

        for (int dayId = 0; dayId < 5; dayId++) {
            // init line 1, line 2 for normal and ot respectively
            ArrayList<Double> nline1 = new ArrayList<Double>(); // a,b,c,d
            ArrayList<Double> nline2 = new ArrayList<Double>();
            ArrayList<Double> otline1 = new ArrayList<Double>();
            ArrayList<Double> otline2 = new ArrayList<Double>();
            for (int i = 0; i < 4; i++) {
                nline1.add(0.0);
                nline2.add(0.0);
                otline1.add(0.0);
                otline2.add(0.0);
            }

            // for normal shift
            ArrayList<Long> normalDemandThisDay = getEachProductNormalDemandForOneDay(dailyDMPS, dayId);
            ArrayList<Double> normalDemandHrList = getEachProductDemandForOneDayHr(normalDemandThisDay);
            Double highNormalHr = getHighest(normalDemandHrList);
            int highestIndex = getHighestIndex(normalDemandHrList);

            // schedule for normal shift
            ArrayList<Double> productOnLine1 = new ArrayList<Double>(); // the order is adding order, size = current # of products on line1
            productOnLine1.add(highNormalHr); // add the highest on line 1 first

            if (checkNotExceeding90(true, 1, productOnLine1)) {
                // if the highest demand can fit into line 1 without exceeding 90% capacity
                nline1.set(highestIndex, highNormalHr); // put highest on line 1 *****************
                Double lowestNormalHr = getSmallest(normalDemandHrList, highestIndex); // determine product l with lowest demand, which is not the highest demand
                int lowestIndex = getSmallestIndex(normalDemandHrList, highestIndex);

                productOnLine1.add(lowestNormalHr);  // add L onto line 1
                if (checkNotExceeding90(true, 2, productOnLine1)) {
                    // if adding L not exceed 90% capacity
                    nline1.set(lowestIndex, lowestNormalHr); // add the lowest onto line 1 ******************
                    ArrayList<Double> productOnLine2 = new ArrayList<Double>();
                    int index1 = 0, index2 = 1;
                    int counter = 0;
                    for (int i = 0; i < 4; i++) {
                        if (i != lowestIndex && i != highestIndex) {
                            productOnLine2.add(normalDemandHrList.get(i)); // add the remaining 2 products onto line 2
                            if (counter == 0) {
                                index1 = i;
                            } else {
                                index2 = i;
                            }
                            counter++;
                        }
                    }
                    Double excess = getNewDemandAfterMinusExcess(true, productOnLine2); // calculate the excess demand on productOnLine2 
                    if (excess <= 0) {
                        // not exceed, done
                        nline2.set(index1, productOnLine2.get(0)); // add the 2 onto line 2***************
                        nline2.set(index2, productOnLine2.get(1)); // add the 2 onto line 2***************
                    } else {
                        productOnLine1.add(excess);  // add the excess amount onto line 1
                        if (checkNotExceeding90(true, 3, productOnLine1)) {
                            Double item1 = productOnLine2.get(0);
                            Double item2 = productOnLine2.get(1);

                            if (item1 > item2) {
                                nline1.set(index1, excess); // put the excess onto line 1 **********
                                nline2.set(index1, item1 - excess); // put the remaining on line 2 **************
                                nline2.set(index2, item2);
                            } else {
                                nline1.set(index2, excess);
                                nline2.set(index1, item1);
                                nline2.set(index2, item2 - excess);
                            }
                        } else {
                            // remove from line1
                            productOnLine1.remove(excess);
                            // if still exceed, then simply put it on the line2 and highlight
                            nline2.set(index1, productOnLine2.get(0));
                            nline2.set(index2, productOnLine2.get(1));
                            result.setNeedHumanIntervention(true);
                        }
                    }
                } else {
                    // if adding lowest to line 1 will extend 90%
                    // remove lowest from line 1
                    productOnLine1.remove(lowestNormalHr);
                    Double remaining = getDifference(true, highNormalHr);
                    productOnLine1.add(remaining); // add part of lowest onto line 1
                    nline1.set(lowestIndex, remaining);  // put part of lowest onto line 1 **************
                    nline2.set(lowestIndex, lowestNormalHr - remaining);// put the remaining of L onto line 2 ***************
                    ArrayList<Double> productOnLine2 = new ArrayList<Double>();
                    productOnLine2.add(lowestNormalHr - remaining); // add part of lowest onto line 2
                    int index1 = 0, index2 = 1;
                    int counter = 0;
                    for (int i = 0; i < 4; i++) {
                        if (i != lowestIndex && i != highestIndex) {
                            productOnLine2.add(normalDemandHrList.get(i)); // add the remaining 2 products onto line 2
                            if (counter == 0) {
                                index1 = i;
                            } else {
                                index2 = i;
                            }
                            counter++;
                        }
                    }
                    nline2.set(index1, productOnLine2.get(1));
                    nline2.set(index2, productOnLine2.get(2)); // put the other 2 products onto line 2 *************
                    if (checkNotExceeding90(true, 3, productOnLine2)) {
                        //done
                    } else {
                        // return error message for human attention
                        result.setNeedHumanIntervention(true);
                    }
                }
            } else {
                // remove highest from line1
                productOnLine1.remove(highNormalHr);
                Double remaining = getDifference(true, 0.0);
                productOnLine1.add(remaining); // add the part of m onto line 1
                nline1.set(highestIndex, remaining); // put the part of m onto line 1 ****************
                ArrayList<Double> productOnLine2 = new ArrayList<Double>();
                productOnLine2.add(highNormalHr - remaining); // add the remain of M onto line 2
                nline2.set(highestIndex, productOnLine2.get(0)); // put the remain of M onto line 2 *************
                int index1 = 0, index2 = 1, index3 = 2;
                int counter = 0;
                for (int i = 0; i < 4; i++) {
                    if (i != highestIndex) {
                        productOnLine2.add(normalDemandHrList.get(i)); // add the remaining 3 products onto line 2
                        if (counter == 0) {
                            index1 = i;
                        } else if (counter == 1) {
                            index2 = i;
                        } else {
                            index3 = i;
                        }
                        counter++;
                    }
                }
                nline2.set(index1, productOnLine2.get(1));
                nline2.set(index2, productOnLine2.get(2));
                nline2.set(index3, productOnLine2.get(3));
                if (checkNotExceeding90(true, 4, productOnLine2)) {
                    // done
                } else {
                    // hight line for human attention
                    result.setNeedHumanIntervention(true);
                }
            }

            // put the schedule result from nline1 and nline2 into result for oneday
            for (int i = 0; i < 4; i++) {
                // for normal shift
                result.getScheduledNormalResults().get(dayId).get(i).set(0, nline1.get(i));
                result.getScheduledNormalResults().get(dayId).get(i).set(1, nline2.get(i));
                // set capacity
                result.getNormalScheduledCapacities().get(dayId).set(0, returnCapacity(true, nline1));
                result.getNormalScheduledCapacities().get(dayId).set(1, returnCapacity(true, nline2));
                // set utilization
                result.getNormalScheduledUtilization().get(dayId).set(0, returnUtilization(true, nline1));
                result.getNormalScheduledUtilization().get(dayId).set(1, returnUtilization(true, nline2));
            }

            // for overtime shift
            ArrayList<Long> otDemandThisDay = getEachProductOvertimeDemandForOneDay(dailyDMPS, dayId);
            ArrayList<Double> otDemandHrList = getEachProductDemandForOneDayHr(otDemandThisDay);
            Double highOTHr = getHighest(otDemandHrList);
            int highestOTIndex = getHighestIndex(otDemandHrList);

            // schedule for ot shift
            ArrayList<Double> productionOnLineOT1 = new ArrayList<Double>();
            productionOnLineOT1.add(highOTHr);

            if (checkNotExceeding90(false, 1, productionOnLineOT1)) {
                // if the highest demand can fit into line 1 without exceeding 90% capacity
                otline1.set(highestOTIndex, highOTHr); // put highest on line 1 *****************
                Double lowestOTHr = getSmallest(otDemandHrList, highestOTIndex); // determine product l with lowest demand, which is not the highest demand
                int lowestOTIndex = getSmallestIndex(otDemandHrList, highestOTIndex);

                productionOnLineOT1.add(lowestOTHr);  // add L onto line 1
                if (checkNotExceeding90(false, 2, productionOnLineOT1)) {
                    // if adding L not exceed 90% capacity
                    otline1.set(lowestOTIndex, lowestOTHr); // add the lowest onto line 1 ******************
                    ArrayList<Double> productionOnLineOT2 = new ArrayList<Double>();
                    int index1 = 0, index2 = 1;
                    int counter = 0;
                    for (int i = 0; i < 4; i++) {
                        if (i != lowestOTIndex && i != highestOTIndex) {
                            productionOnLineOT2.add(otDemandHrList.get(i)); // add the remaining 2 products onto line 2
                            if (counter == 0) {
                                index1 = i;
                            } else {
                                index2 = i;
                            }
                            counter++;
                        }
                    }
                    Double excess = getNewDemandAfterMinusExcess(false, productionOnLineOT2); // calculate the excess demand on productOnLine2 
                    if (excess <= 0) {
                        // not exceed, done
                        otline2.set(index1, productionOnLineOT2.get(0)); // add the 2 onto line 2***************
                        otline2.set(index2, productionOnLineOT2.get(1)); // add the 2 onto line 2***************
                    } else {
                        productionOnLineOT1.add(excess);  // add the excess amount onto line 1
                        if (checkNotExceeding90(false, 3, productionOnLineOT1)) {
                            Double item1 = productionOnLineOT2.get(0);
                            Double item2 = productionOnLineOT2.get(1);

                            if (item1 > item2) {
                                otline1.set(index1, excess); // put the excess onto line 1 **********
                                otline2.set(index1, item1 - excess); // put the remaining on line 2 **************
                                otline2.set(index2, item2);
                            } else {
                                otline1.set(index2, excess);
                                otline2.set(index1, item1);
                                otline2.set(index2, item2 - excess);
                            }

                        } else {
                            // remove from line1
                            productionOnLineOT1.remove(excess);
                            // if still exceed, then simply put it on the line2 and highlight
                            otline2.set(index1, productionOnLineOT2.get(0));
                            otline2.set(index2, productionOnLineOT2.get(1));
                            result.setNeedHumanIntervention(true);
                        }
                    }
                } else {
                    // if adding lowest to line 1 will extend 90%
                    // remove lowest from line 1
                    productionOnLineOT1.remove(lowestOTHr);
                    Double remaining = getDifference(false, highOTHr);
                    productionOnLineOT1.add(remaining); // add part of lowest onto line 1
                    otline1.set(lowestOTIndex, remaining);  // put part of lowest onto line 1 **************
                    otline2.set(lowestOTIndex, lowestOTHr - remaining);// put the remaining of L onto line 2 ***************
                    ArrayList<Double> productionOnLineOT2 = new ArrayList<Double>();
                    productionOnLineOT2.add(lowestOTHr - remaining); // add part of lowest onto line 2
                    int index1 = 0, index2 = 1;
                    int counter = 0;
                    for (int i = 0; i < 4; i++) {
                        if (i != lowestOTIndex && i != highestOTIndex) {
                            productionOnLineOT2.add(otDemandHrList.get(i)); // add the remaining 2 products onto line 2
                            if (counter == 0) {
                                index1 = i;
                            } else {
                                index2 = i;
                            }
                            counter++;
                        }
                    }
                    otline2.set(index1, productionOnLineOT2.get(1));
                    otline2.set(index2, productionOnLineOT2.get(2)); // put the other 2 products onto line 2 *************
                    if (checkNotExceeding90(false, 3, productionOnLineOT2)) {
                        //done
                    } else {
                        // reture error message for human attention
                        result.setNeedHumanIntervention(true);
                    }
                }
            } else {
                // remove highest from line1
                productionOnLineOT1.remove(highOTHr);
                Double remaining = getDifference(false, 0.0);
                productionOnLineOT1.add(remaining); // add the part of m onto line 1
                otline1.set(highestOTIndex, remaining); // put the part of m onto line 1 ****************
                ArrayList<Double> productionOnLineOT2 = new ArrayList<Double>();
                productionOnLineOT2.add(highOTHr - remaining); // add the remain of M onto line 2
                otline2.set(highestOTIndex, productionOnLineOT2.get(0)); // put the remain of M onto line 2 *************
                int index1 = 0, index2 = 1, index3 = 2;
                int counter = 0;
                for (int i = 0; i < 4; i++) {
                    if (i != highestOTIndex) {
                        productionOnLineOT2.add(otDemandHrList.get(i)); // add the remaining 3 products onto line 2
                        if (counter == 0) {
                            index1 = i;
                        } else if (counter == 1) {
                            index2 = i;
                        } else {
                            index3 = i;
                        }
                        counter++;
                    }
                }
                otline2.set(index1, productionOnLineOT2.get(1));
                otline2.set(index2, productionOnLineOT2.get(2));
                otline2.set(index3, productionOnLineOT2.get(3));
                if (checkNotExceeding90(false, 4, productionOnLineOT2)) {
                    // done
                } else {
                    // hight line for human attention
                    result.setNeedHumanIntervention(true);
                }
            }

            // put the schedule result from nline1 and nline2 into result for oneday
            for (int i = 0; i < 4; i++) {
                // for normal shift
                result.getScheduledOvertimeResults().get(dayId).get(i).set(0, otline1.get(i));
                result.getScheduledOvertimeResults().get(dayId).get(i).set(1, otline2.get(i));
                // set capacity
                result.getOvertimeScheduledCapacities().get(dayId).set(0, returnCapacity(false, otline1));
                result.getOvertimeScheduledCapacities().get(dayId).set(1, returnCapacity(false, otline2));
                // set utilization
                result.getOvertimeScheduledUtilization().get(dayId).set(0, returnUtilization(false, otline1));
                result.getOvertimeScheduledUtilization().get(dayId).set(1, returnUtilization(false, otline2));
            }

        }
        return result;
    }

    @Override
    public Double returnCapacity(boolean normal, ArrayList<Double> line) {
        int count = 0;
        for (Double d : line) {
            if (!d.equals(0.0)) {
                count++;
            }
        }
        if (count > 0) {
            count--;
        }
        if (normal) {
            return Consts.NORMAL_SHIFT_PER_DAY / 2 - Consts.EACH_SHIFT_COST_TIME * count;
        } else {
            return Consts.OVERTIME_EXTRA_SHIFT_PER_DAY / 2 - Consts.EACH_SHIFT_COST_TIME * count;
        }
    }

    @Override
    public Double returnUtilization(boolean normal, ArrayList<Double> line) {
        int count = 0;
        double sum = 0d;
        for (Double d : line) {
            if (!d.equals(0.0)) {
                count++;
            }
            sum += d;
        }
        if (count > 0) {
            count--;
        }
        if (normal) {
            return sum / (Consts.NORMAL_SHIFT_PER_DAY / 2 - Consts.EACH_SHIFT_COST_TIME * count);
        } else {
            return sum / (Consts.OVERTIME_EXTRA_SHIFT_PER_DAY / 2 - Consts.EACH_SHIFT_COST_TIME * count);
        }
    }

    private Double getDifference(boolean normal, Double highest) {
        Double remaining;
        if (normal) {
            remaining = (Consts.NORMAL_SHIFT_PER_DAY / 2 - Consts.EACH_SHIFT_COST_TIME * 1) * 0.9 - highest;
        } else {
            remaining = (Consts.OVERTIME_EXTRA_SHIFT_PER_DAY / 2 - Consts.EACH_SHIFT_COST_TIME * 1) * 0.9 - highest;
        }
        return remaining;
    }

    private Double getNewDemandAfterMinusExcess(boolean normal, ArrayList<Double> productOnLine) {
        int length = productOnLine.size();
        Double sum = 0.0;
        for (Double p : productOnLine) {
            sum += p;
        }
        if (length > 0) {
            length--;
        }
        Double excess;
        if (normal) {
            excess = sum - (Consts.NORMAL_SHIFT_PER_DAY / 2 - Consts.EACH_SHIFT_COST_TIME * length) * 0.9;
        } else {
            excess = sum - (Consts.OVERTIME_EXTRA_SHIFT_PER_DAY / 2 - Consts.EACH_SHIFT_COST_TIME * length) * 0.9;
        }
        return excess;
    }

    private Double getHighest(ArrayList<Double> list) {
        if (list == null || list.isEmpty()) {
            return null;
        }
        Double highest = list.get(0);
        for (Double d : list) {
            if (highest <= d) {
                highest = d;
            }
        }
        return highest;
    }

    private int getHighestIndex(ArrayList<Double> list) {
        if (list == null || list.isEmpty()) {
            return -1;
        }
        int result = 0;
        Double highest = list.get(0);
        for (int i = 0; i < list.size(); i++) {
            if (highest <= list.get(i)) {
                highest = list.get(i);
                result = i;
            }
        }
        return result;
    }

    // return the smallest demand hr which is not the prev highest demand
    private Double getSmallest(ArrayList<Double> list, int highestIndex) {
        if (list == null || list.isEmpty()) {
            return null;
        }
        int index = 0;
        if (highestIndex == 0) {
            index = 1;
        } else if (highestIndex == 3) {
            index = 1;
        } else {
            index = highestIndex - 1;
        }
        Double smallest = list.get(index);
        index = 0;
        for (Double d : list) {
            if (smallest > d && index != highestIndex) {
                smallest = d;
            }
            index++;
        }
        return smallest;
    }

    private int getSmallestIndex(ArrayList<Double> list, int highestIndex) {
        if (list == null || list.isEmpty()) {
            return -1;
        }
        int index = 0;
        if (highestIndex == 0) {
            index = 1;
        } else if (highestIndex == 3) {
            index = 1;
        } else {
            index = highestIndex - 1;
        }
        Double smallest = list.get(index);
        index = 0;
        int counter = 0;
        for (Double d : list) {
            if (smallest >= d && counter != highestIndex) {
                smallest = d;
                index = counter;
            }
            counter++;
        }
        return index;
    }

    private ArrayList<Double> getEachProductDemandForOneDayHr(ArrayList<Long> demandThisDay) {
        ArrayList<Double> result = new ArrayList<Double>();
        for (int i = 0; i < demandThisDay.size(); i++) {
            Double hr;
            if (i == 0 || i == 1) {
                hr = demandThisDay.get(i).doubleValue() / Consts.CASES_PER_HOUR_CLASSIC;
            } else {
                hr = demandThisDay.get(i).doubleValue() / Consts.CASES_PER_HOUR_PREMIUM;
            }
            result.add(hr);
        }
        return result;
    }

    private boolean checkNotExceeding90(boolean normal, int numOfProductOnLine, ArrayList<Double> productsOnLine) {
        // productsOnLine 's size = numOfProductOnline, order : adding order
        if (numOfProductOnLine > 0) {
            numOfProductOnLine--;
        }
        Double baseTotal; // the dividand
        if (normal) {
            // for normal
            baseTotal = (Consts.NORMAL_SHIFT_PER_DAY / 2) - Consts.EACH_SHIFT_COST_TIME * numOfProductOnLine;
        } else {
            // for overtime
            baseTotal = (Consts.OVERTIME_EXTRA_SHIFT_PER_DAY / 2) - Consts.EACH_SHIFT_COST_TIME * numOfProductOnLine;
        }
        Double sum = 0.0;
        for (Double indiv : productsOnLine) {
            sum += indiv;
        }
        if (sum / baseTotal > 0.9) {
            return false;
        } else {
            return true;
        }
    }

    private Double getHighestHr(ArrayList<Long> demandThisDay) {
        Double highest = Double.MIN_VALUE;
        for (int i = 0; i < demandThisDay.size(); i++) {
            Double hr;
            if (i == 0 || i == 1) {
                hr = demandThisDay.get(i).doubleValue() / Consts.CASES_PER_HOUR_CLASSIC;
            } else {
                hr = demandThisDay.get(i).doubleValue() / Consts.CASES_PER_HOUR_PREMIUM;
            }
            if (hr > highest) {
                highest = hr;
            }
        }
        return highest;
    }

    private Double getLowestHr(ArrayList<Long> demandThisDay) {
        Double lowest = Double.MAX_VALUE;
        for (int i = 0; i < demandThisDay.size(); i++) {
            Double hr;
            if (i == 0 || i == 1) {
                hr = demandThisDay.get(i).doubleValue() / Consts.CASES_PER_HOUR_CLASSIC;
            } else {
                hr = demandThisDay.get(i).doubleValue() / Consts.CASES_PER_HOUR_PREMIUM;
            }
            if (hr < lowest) {
                lowest = hr;
            }
        }
        return lowest;
    }

    private ArrayList<Long> getEachProductNormalDemandForOneDay(DailyMPS dmps, int dayId) {
        ArrayList<Long> result = new ArrayList<Long>();
        for (int i = 0; i < 4; i++) {
            result.add(dmps.returnIndivDMPS(i).returnDailyPlannedProduction(dayId).getDailyDemand());
        }
        return result;
    }

    private ArrayList<Long> getEachProductOvertimeDemandForOneDay(DailyMPS dmps, int dayId) {
        ArrayList<Long> result = new ArrayList<Long>();
        for (int i = 0; i < 4; i++) {
            result.add(dmps.returnIndivDMPS(i).returnDailyPlannedProduction(dayId).getDailyOTDemand());
        }
        return result;
    }

    @Override
    public Collection<DailyMPS> getAllDailyMPS() {
        Query query = entityManager.createQuery("SELECT dmps FROM DailyMPS dmps");
        return query.getResultList();
    }

    //for calculate
    @Override
    public boolean checkRawMaterial(DailyMPS dailyMPS) {

        System.out.println("#################### mpsSession at line 1269: rawMaterial called");


        if (dailyMPS == null) {
            System.out.println("#################### mpsSession at line 1273: returned true");
            return true;
        }

        // check raw material using dailyPlannedProduction in dailyMPS
        // return: true - enough; false- not enough
        ArrayList<Long> pRequirements = new ArrayList<Long>(); //product requirements: arraylist of size 4. 0->A, 1->B, 2->C, 3->D
        for (int i = 0; i < 4; i++) {
            Long count = 0l;
            for (int j = 0; j < 5; j++) {
                count += dailyMPS.returnIndivDMPS(i).returnDailyPlannedProduction(j).getTotalDemand();
            }
            pRequirements.add(count);
        }

        //calculate requirements for each material and check whether it is enough  
        for (String materialName : Consts.allRawMaterialNames) {
            //get the corresponding mrp
            MRP mrp = mrpSession.getMRPByDateAndMaterial(dailyMPS.getStartPeriodYear(), dailyMPS.getStartPeriodMonth(), materialName);
            Long planned = mrp.getGrossRequirements().get(dailyMPS.getStartPeriodDate() - 1).getLongValue() + mrp.getPlannedInventory().get(dailyMPS.getStartPeriodDate() - 1).getLongValue();
            BOM bom = mrpSession.getBOM(materialName);
            double mRequirements = 0; //total requirements for a type of material
            for (int j = 0; j < 4; j++) { //for each product
                double kg;
                if (j == 0 || j == 1) {
                    kg = 12;
                } else {
                    kg = 13.32;
                }
                mRequirements += pRequirements.get(j) * kg * bom.getMaterialRequirements().get(j).getValue();
            }

            System.out.println("##################RawMaterial at mpsSession line 1301" + materialName + " " + mRequirements + " " + planned);
            if (mRequirements > planned) {
                System.out.println("mpsSession at line 1303 : " + "Entered if ");
                return false;
            }
        }
        return true;
    }

    //after 10.26  for feedback
    @Override
    public boolean updateDailyMPS2(DailyMPS updatedDMPS, ArrayList<ArrayList<Long>> prevDailyDemandList, ArrayList<ArrayList<Long>> prevDailyOTDemandList) {
        System.out.println("update daily mps 2 ******************** 1318 mps p session");
        // UPDATED: 1. update w mps, remember to merge
        updateWMPSTriggerByDMPS(updatedDMPS);
        System.out.println("update daily mps 2 ******************** 1321 mps p session: finish calling dmps -> wmps");

        // UPDATED: 2. update sop ( i)subtract old with new and get the difference as unplanned demand; ii) create archived sops, use the 3rd constructor
        // ), remember to merge

        updateSOPTriggerByDMPS(updatedDMPS, prevDailyDemandList, prevDailyOTDemandList);

        System.out.println("update daily mps 2 ******************** 1321 mps p session: finish calling dmps -> sop");

        SOP sop = getSOPForMPS(updatedDMPS.getStartPeriodMonth(), updatedDMPS.getStartPeriodYear());
        // if the dmps is updated, don't allow modification of sop
        //sop.setAbleToEdit(Boolean.FALSE);
        updatedDMPS.setSop(sop);
        entityManager.merge(updatedDMPS);
        return true;
    }

    @Override
    public WeeklyMPS createWeeklyMPS(int selectedYear, Integer selectedMonth) {
        WeeklyMPS wmps = new WeeklyMPS(selectedYear, selectedMonth);
        ArrayList<ArrayList<Long>> weeklyDemandForAll = getWeeklyDemandForOneMonth(selectedMonth, selectedYear);
        for (int i = 0; i < 4; i++) {
            for (int j = 0; j < 5; j++) {
                wmps.returnProductIndivSOP(i).getWeeklyDemandList().get(j).setLongValue(weeklyDemandForAll.get(i).get(j));
            }
        }
        entityManager.merge(wmps);
        return wmps;
    }

    @Override
    public WeeklyMPS updateWeeklyMPS(int selectedYear, Integer selectedMonth) {
        WeeklyMPS wmps = getWeeklyMPS(selectedYear, selectedMonth);

        if (wmps == null) {
            return null;
        }
        ArrayList<ArrayList<Long>> weeklyDemandForAll = getWeeklyDemandForOneMonth(selectedMonth, selectedYear);
        for (int i = 0; i < 4; i++) {
            for (int j = 0; j < 5; j++) {
                wmps.returnProductIndivSOP(i).getWeeklyDemandList().get(j).setLongValue(weeklyDemandForAll.get(i).get(j));
            }
        }
        entityManager.merge(wmps);
        return wmps;
    }

    @Override
    public WeeklyMPS getWeeklyMPS(int selectedYear, int selectedMonth) {
        Integer y = new Integer(selectedYear);
        Integer m = new Integer(selectedMonth);

        try {
            Query query = entityManager.createQuery("SELECT wmps FROM WeeklyMPS wmps WHERE wmps.startPeriodYear=:p1 AND wmps.startPeriodMonth=:p2");
            query.setParameter("p1", y);
            query.setParameter("p2", m);
            if (query.getResultList().isEmpty()) {
                return null;
            }
            return (WeeklyMPS) query.getResultList().get(0);
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }

    @Override
    public DailyMPS createDailyMPS(int selectedYear, Integer selectedMonth, Integer weekNo) {
        ArrayList<ArrayList<Long>> dailyDemand = getEachDayDemandForOneWeek(selectedYear, selectedMonth, weekNo);
        ArrayList<Timestamp> times = getDatesforWeek(selectedYear, selectedMonth, weekNo);
        Calendar cal = Calendar.getInstance();
        DailyMPS dmps = new DailyMPS(selectedYear, selectedMonth, weekNo, new Timestamp(cal.getTimeInMillis()));
        //update demand list
        for (int i = 0; i < dailyDemand.size(); i++) {
            Product p = getProduct(i);
            for (int j = 0; j < dailyDemand.get(i).size(); j++) {
                dmps.returnIndivDMPS(i).returnDailyPlannedProduction(j).setDailyDemand(dailyDemand.get(i).get(j));
                dmps.returnIndivDMPS(i).returnDailyPlannedProduction(j).setProductionDay(times.get(j));
                dmps.returnIndivDMPS(i).returnDailyPlannedProduction(j).setProduct(p);
            }
        }
        int month = 0;
        if (selectedMonth < 4) {
            month = 1;
        } else if (selectedMonth < 7) {
            month = 4;
        } else if (selectedMonth < 10) {
            month = 7;
        } else {
            month = 10;
        }
        SOP sop = sopSession.getSOP(selectedYear, month);
        dmps.setSop(sop);
        entityManager.merge(dmps);

        dmps = getDailyMPS(selectedMonth, selectedMonth, weekNo);
        return dmps;
    }

    //everage done
    @Override
    public DailyMPS updateDailyMPS(int selectedYear, Integer selectedMonth, Integer weekNo) {
        ArrayList<ArrayList<Long>> dailyDemand = getEachDayDemandForOneWeek(selectedYear, selectedMonth, weekNo);
        DailyMPS dmps = getDailyMPS(selectedYear, selectedMonth, weekNo);
        if (dmps == null) {
            return null;
        }
        //update demand list
        for (int i = 0; i < 4; i++) {
            for (int j = 0; j < 5; j++) {
                dmps.returnIndivDMPS(i).returnDailyPlannedProduction(j).setDailyDemand(dailyDemand.get(i).get(j));

            }
        }
        entityManager.merge(dmps);

        dmps = getDailyMPS(selectedMonth, selectedMonth, weekNo);
        return dmps;
    }

    public ArrayList<DailyPlannedProduction> getAllDailyPlannedProduction() {
        Query query = entityManager.createQuery("SELECT dpp FROM DailyPlannedProduction dpp");
        return new ArrayList<DailyPlannedProduction>(query.getResultList());
    }

    // TODO: reservation: only set quantity to 0?
    @Override
    public boolean unreservePlannedProduction(DeliveryOrder order) {
        try {
            Query query = entityManager.createQuery("SELECT r FROM Reservation r WHERE r.deliveryOrder.id = :p1");
            query.setParameter("p1", order.getId());
            ArrayList<Reservation> reservations = new ArrayList<Reservation>(query.getResultList());
            if (!query.getResultList().isEmpty()) {
                for (Reservation r : reservations) {
                    ArrayList<DailyPlannedProduction> dpps = getAllDailyPlannedProduction();
                    Iterator dppsIterator = dpps.iterator();
                    while (dppsIterator.hasNext()) {
                        DailyPlannedProduction dpp = (DailyPlannedProduction) dppsIterator.next();
                        if (dpp.getReservations().contains(r)) {
                            dpp.getReservations().remove(r);
                        }
                        entityManager.merge(dpp);

                    }
                }
                int size = reservations.size();
                for (int i = 0; i < size; i++) {
                    entityManager.remove(reservations.get(i));
                    entityManager.flush();
                }
            }
            return true;
        } catch (Exception e) {
            return false;
        }

    }
//    @Override
//    public WeeklyMPS getWeeklyMPS(int startYear, int startMonth) {
//        Integer year = new Integer(startYear);
//        Integer month = new Integer(startMonth);
//        
//        try {
//            Query query = entityManager.createQuery("SELECT wmps FROM WeeklyMPS wmps WHERE wmps.startPeriodYear=:p1 AND wmps.startPeriodMonth=:p2");
//            query.setParameter("p1", year);
//            query.setParameter("p2", month);
//        } catch (Exception ex) {
//            ex.printStackTrace();
//            return null;
//        }
//    }
}
